package com.whjz.dcfs.pub.excel;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.ClassUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Pattern;

/**
 * Excel工具类
 *
 *
 */
public final class ExcelUtils {
    /**
     * Excel工具类 LOG
     */
    protected final static Logger LOG = LoggerFactory.getLogger(ExcelUtils.class);
    public static final int IMPORT_NUM_MAX = 5000;
    public static final Pattern NumberRange_Pattern = Pattern.compile("^[\\[\\(](\\-∞)?[0-9]*,(\\+∞)?[0-9]*[\\]\\)]$");

    private ExcelUtils() {

    }

    /**
     *
     * @param is        文件流对象
     * @param type      Excel行对应的Java对象
     * @param startRow  从该参数指定的行开始读取数据
     * @param <T>       泛型参数
     * @return Excel数据
     */
    public static <T> ExcelData<T> read(InputStream is,final Class<T> type, final int startRow) {
    	return read(is, type, startRow, IMPORT_NUM_MAX, null);
    }
    
    public static <T> ExcelData<T> read(InputStream is,final Class<T> type, final int startRow,final int checkSize) {
    	return read(is, type, startRow, checkSize, null);
    }
    public static <T> ExcelData<T> read(InputStream is,final Class<T> type, final int startRow,final int checkSize,
                                        final List<String> columnStrs) {
        List<List<String>> excelData = null;
        try {
            IExcelParser parser = new XLSParser();
            excelData = parser.parse(is);
        } catch (final Exception e) {
            throw new ExcelParseException("Excel文件损坏，无法读取。", e);
        }
        return read(excelData, type, startRow, checkSize, columnStrs);
    }
    public static <T> ExcelData<T> read(Sheet sheet, final Class<T> type, final int startRow,final int checkSize,final List<String> columnStrs) {
        List<List<String>> excelData = null;
        try {
            IExcelParser parser = new XLSParser();
            excelData = parser.parse(sheet);
        } catch (final Exception e) {
            throw new ExcelParseException("Excel文件损坏，无法读取。", e);
        }
        return read(excelData, type, startRow, checkSize, columnStrs);
    }
    private static <T> ExcelData<T> read(List<List<String>> excelData, final Class<T> type, final int startRow,final int checkSize,
                                         final List<String> columnStrs) {
        final ExcelData<T> dataResult = new ExcelData<T>();
        String errorInfo = "";
        boolean errorSign = false;
        int len = excelData.size();
        if(len>(startRow+checkSize))
            throw new ExcelParseException("上传数量超出限制("+checkSize+"条)");
        List<String> headers = new LinkedList<String>();
        for(String s : excelData.get(0)){
        	if(null != s)
        		headers.add(s.trim());
        }

        for (int row = startRow; row < len; row++) {
            List<String> rowData = excelData.get(row);
            if(rowData.size() <=0)continue;
            T obj = null;
            try {
                obj = type.newInstance();
            } catch (Exception e1) {
                LOG.error("解析Excel时，实例化模型类失败", e1);
                throw new ExcelParseException("Excel Model实例化错误，请确保是否存在无参的构造函数。");
            }
            boolean isAllBlank = true; // 整行记录为空标志
        	int rowSize = rowData.size();
            List<Field> list = CollectionUtils.asList(type.getDeclaredFields());
        	Class _t = type.getSuperclass();
        	while(_t != null){
        		list.addAll(Arrays.asList(_t.getDeclaredFields()));
        		_t = _t.getSuperclass();
        	}
            for (final Field field : list) {
            	String fieldName = field.getName();
                try {
                	if(fieldName.equals("excelNo")){
                        PropertyUtils.setProperty(obj, "excelNo", row+1);
                        continue;
                	}
                	//如果定义了解析列列表，则忽略掉不在列表中的数据列
                	if(columnStrs != null && !columnStrs.contains(fieldName))
                		continue;
                	
                    final ExcelColumn e = field.getAnnotation(ExcelColumn.class);
                    if (e != null) {
                    	int pos = e.pos();
                    	if(pos == -1){
                    		if(e.name().equals(""))
                    			throw new ExcelParseException("Excel列["+fieldName+"]配置项缺失,pos和name不能都为空");
                    		pos = headers.indexOf(e.name());
                    		if(pos == -1)
                    			if(e.required())
                    				throw new ExcelParseException("列["+e.name()+"]丢失,请添加此列或重新下载模板");
                    			else
                    				continue;
                    	}else{
                    		if(!e.name().equals("") && !e.name().equals(headers.get(pos)))
                    			throw new ExcelParseException("Excel列信息有误,第"+(pos+1)+"列期望为["+e.name()+"],实际为["+headers.get(pos)+"]");
                    	}
                        String content = pos<rowSize?rowData.get(pos):null;
                        if (isAllBlank && StringUtils.isNotBlank(content)) {
                            isAllBlank = false;
                        }
                        Object parseValue = parseElement(e,content,headers.get(pos));
                        PropertyUtils.setProperty(obj, field.getName(),parseValue);
                        //数据格式验证
                        final ExcelColumnValidator ve = field.getAnnotation(ExcelColumnValidator.class);
                        if (ve != null && parseValue != null) {
                        	validateElement(ve,parseValue,headers.get(pos));
                        }
                    }
                } catch(ExcelParseException e1){
                	errorInfo += ("表格第"+(row+1)+"行:"+(e1.getMessage()==null?e1.getCause().getMessage():e1.getMessage())+";");
                	errorSign = true;
                	continue;
                } catch (final Exception e1) {
                    LOG.error("填充模型数据时出错", e1);
                	e1.printStackTrace();
                	errorInfo += ("表格第"+(row+1)+"行:"+(e1.getMessage()==null?e1.getCause().getMessage():e1.getMessage())+";");
                	errorSign = true;
                	continue;
                }
            }
            if (isAllBlank){// 如果整行记录为空，则忽略该行数据
                continue;
            }
        	if(!errorSign)
        		dataResult.getResultList().add(obj);
        }
        if(errorSign)
        	throw new ExcelParseException(errorInfo.substring(0,errorInfo.length()-1));
        return dataResult;
    }
    private static void validateElement(ExcelColumnValidator ve,Object parseValue,String headName) throws ExcelParseException{
    	if(parseValue == null)
    		return;
    	if(ve.length() > 0){
    		if(parseValue.toString().length() > ve.length())
    			throw new ExcelParseException(headName+"长度不允许超过" + ve.length());
    	}
    	if(ve.boundValues().length > 0){
    		List boundVs = Arrays.asList(ve.boundValues());
    		for(String s : parseValue.toString().split("\\|")){
    			if(!boundVs.contains(s))
        			throw new ExcelParseException("列["+headName+"]存在不允许的内容:[" + s +"]");
    		}
    	}
    	if(!ve.numberRange().equals("")){
    		Double _d = null;
    		try{
    			_d = Double.parseDouble(parseValue.toString());
    		}catch(NumberFormatException e){
    			throw new ExcelParseException(headName+"列内容需为数字");
    		}
    		String rangeStr = ve.numberRange();
    		if(!NumberRange_Pattern.matcher(rangeStr).matches())
    			throw new ExcelParseException(headName+"数值范围配置项有误");
    		int seperatorIndex = rangeStr.indexOf(",");
    		String _s1 = rangeStr.substring(0, 1);
    		String _s2 = rangeStr.substring(rangeStr.length()-1, rangeStr.length());
    		String _s3 = rangeStr.substring(1,seperatorIndex);
    		String _s4 = rangeStr.substring(seperatorIndex+1,rangeStr.length()-1);
    		int _v1 = (_s3.equals("")||_s3.equals("-∞"))?Integer.MIN_VALUE:Integer.parseInt(_s3);
    		int _v2 = (_s4.equals("")||_s4.equals("+∞"))?Integer.MAX_VALUE:Integer.parseInt(_s4);
    		if(_s1.equals("(")){
    			if(_d<=_v1)
        			throw new ExcelParseException(headName+"内容应大于"+_v1);
    		}else if(_s1.equals("[")){
    			if(_d<_v1)
        			throw new ExcelParseException(headName+"内容应大于等于"+_v1);
    		}
    		if(_s2.equals(")")){
    			if(_d>=_v2)
        			throw new ExcelParseException(headName+"内容应小于"+_v2);
    		}else if(_s2.equals("]")){
    			if(_d>_v2)
        			throw new ExcelParseException(headName+"内容应小于等于"+_v2);
    		}
    	}
    }
    /**
     * 根据注解，将Excel单元格内容更新到Model
     *
     * @param e
     * @param content
     * @throws Exception
     */
    private static <T> Object parseElement(final ExcelColumn e,String content,String header)
            throws Exception {
        if (e != null) {
            if (StringUtils.isBlank(content)) {
                if (e.required()) {
                    throw new ExcelParseException(String.format("%s不能为空!", header));
                }
                return null;
            }
            try{
            	return StringUtils.isBlank(content)?null:convertCotent(content.trim(), e);
            }catch(ExcelParseException ec){
            	throw  ec;
            }catch(Exception ec){
            	LOG.error(ec.getMessage());
                throw new ExcelParseException("解析内容时发生错误,请检查格式是否正确["+header+"]");
            }
        }
        return null;
    }

    /**
     * 根据注解，将字符串转换为不同的数据类型格式
     *
     * @param content
     * @param metaColumn
     * @return
     * @throws Exception
     */
    private static Object convertCotent(String content, ExcelColumn metaColumn) throws Exception {
        Class propType = metaColumn.propType();
        if (Boolean.class.equals(propType)) {
            return Boolean.valueOf(content);
        } else if (ClassUtils.primitiveToWrapper(propType).equals(Character.class)) {
            return content.charAt(0);
        } else if (Integer.class.isAssignableFrom(ClassUtils.primitiveToWrapper(propType))) {// Excel读取出的数字默认为double格式
        	if(content.indexOf(".")>-1)
        		throw new ExcelParseException("列["+metaColumn.name()+"]类型应为整数");
            return Integer.parseInt(content);
        } else if (Long.class.isAssignableFrom(ClassUtils.primitiveToWrapper(propType))) {
            return content.indexOf(".") != -1 ? Double.valueOf(content).longValue() : Long.parseLong(content);
        } else if (Double.class.isAssignableFrom(ClassUtils.primitiveToWrapper(propType))) {
            return Double.valueOf(content);
        } else if (propType.equals(String.class)) {
            return content.trim();
        } else if (java.util.Date.class.isAssignableFrom(propType)) {
            return DateUtils.parseDate(content, metaColumn.format().split(","));
        } else if (propType.equals(BigDecimal.class)) {
            return new BigDecimal(content);
        } else if (propType.equals(Float.class)) {
            return Float.parseFloat(content);
        } else {
            throw new ExcelParseException("数据类型不支持:" + propType);
        }
    }
}
